import pymysql
from common.read_yaml import ReadYamlData,ReadYamlConfigs
# data = ReadYamlConfigs('data.yaml').get_yaml_data()
# db = data['db']

db=ReadYamlConfigs('data.yaml').get_yaml_data()['db']
#获取连接方法
def get_db_conn():
    conn = pymysql.connect(
        host = db['host'],
        port = 3306,
        user = db['user_name'],
        password = db['password'],
        charset = 'utf8'

    )
    return conn

#查询
def query_db(sql):
    conn = get_db_conn()    #获取连接
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)    #建立游标
    cur.execute(sql)    #执行sql
    conn.commit()
    result = cur.fetchone()     #获取查询结果
    print(result)
    cur.close()     #关闭游标
    conn.close()    #关闭连接
    return result

# 更改
def change_db(sql):
    conn = get_db_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    try:
        cur.execute(sql)  # 执行sql
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭连接0

# 删除
def delete_db(sql):
    conn = get_db_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    try:
        cur.execute(sql)  # 执行sql
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭连接0

if __name__ == '__main__':
    b = (4,16)
    print('rolen in{}'.format(b))

    #query_db(sql="SELECT id,mobile FROM uaa.jhi_user WHERE id in (SELECT user_id FROM live_show_center.user_role where 'role in {}'.format(b))")
    query_db(sql='SELECT id,mobile FROM uaa.jhi_user WHERE id in (SELECT user_id FROM live_show_center.user_role where role in {})'.format(b))
    query_db(sql='SELECT user_id FROM live_show_center.user_role where role in {} order by rand() limit 1'.format(b))



# data = {'sku_id': 5, 'count': 1}
# sql = 'update tb_sku set stock= stock + @data["count"] where sku_id= @data["sku_id"]'